The SQL procedure returns incorrect results from an In-Line View or an SQL View. The incorrect results occur when the following conditions are true:

• The view contains a column(s) created using a summary function
• A new column is created from the summarized column(s) created in the view
• A WHERE clause contains a Calculated reference to the column(s) created in item 2

To see an example of code that produces the incorrect and correct results, click the Full Code tab. Click the Output tab to see the incorrect and correct results.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

title;                                                              
data example;                                                       
class='a'; cnt1=5;  cnt2=0; output;                                 
class='b'; cnt1=23; cnt2=3; output;                                 
class='b'; cnt1=23; cnt2=3; output;                                 
class='c'; cnt1=37; cnt2=4; output;                                 
run;                                                                
                                                                    
                                                                    
title '** incorrect results when an Inline View is used **';        
proc sql;                                                           
  create table incorrect1 as                                        
    select                                                          
      class                                                         
     ,cnt1                                                          
     ,cnt2                                                          
     ,(cnt1 + cnt2) as tot                                          
                                                                    
    from                                                            
      ( select                                                      
          class                                                     
         ,sum(cnt1) as cnt1                                         
         ,sum(cnt2) as cnt2                                         
                                                                    
       from example                                                 
      group by class )                                              
                                                                    
    where calculated tot>0 ;                                        
                                                                    
quit;                                                               
                                                                    
proc print data=incorrect1;                                         
run;                                                                
                                                                    
                                                                    
                                                                    
                                                                    
title '**  incorrect results occur when an SQL view is used  **';   
proc sql;                                                           
  create view temp as                                               
    select                                                          
      class                                                         
     ,sum(cnt1) as cnt11                                            
     ,sum(cnt2) as cnt22                                            
                                                                    
  from example                                                      
 group by class;                                                    
                                                                    
                                                                    
  create table incorrect2 as                                        
    select                                                          
      class                                                         
     ,cnt11                                                         
     ,cnt22                                                         
     ,(cnt11 + cnt22) as tot                                        
                                                                    
    from temp                                                       
                                                                    
   where calculated tot >  0                                        
;                                                                   
                                                                    
quit;                                                               
                                                                    
proc print data=incorrect2;                                         
run;                                                                
                                                                    
                                                                    
                                                                    
                                                                    
title '**  correct results occur when a view is NOT used  **';      
proc sql;                                                           
                                                                    
  create table correct3 as                                          
    select                                                          
      class                                                         
     ,sum(cnt1) as cnt1                                             
     ,sum(cnt2) as cnt2                                             
     ,(calculated cnt1 + calculated cnt2) as tot                    
                                                                    
  from example                                                      
 group by class                                                     
                                                                    
 having calculated tot>0                                            
                                                                    
;                                                                   
                                                                    
quit;                                                               
                                                                    
proc print data=correct3;                                           
run;                                                                
                                                                    
title;                                                              
                                                                    
                                                                    

** incorrect results when an Inline View is used **

Obs    class    cnt1    cnt2    tot

 1       b       46       6       5
 2       c       37       4      52

---------------------------------------------------------

**  incorrect results occur when an SQL view is used  **

Obs    class    cnt11    cnt22    tot

 1       b        46       6        5
 2       c        37       4       52

---------------------------------------------------------

**  correct results occur when a view is NOT used  **

Obs    class    cnt1    cnt2    tot

 1       a        5       0       5
 2       b       46       6      52
 3       c       37       4      41

A fix for this issue for Base SAS 9.4_M3 is available at:

A fix for this issue for Base SAS 9.3_M2 is available at: